﻿<%@ WebHandler Language="C#" Class="ContainerShipRecordReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 转工数据查询报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:
 **/

public class ContainerShipRecordReport : IHttpHandler
{
    private uMESReportOracleDAL dal = new uMESReportOracleDAL();
    private UserImage u = new UserImage();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询方法
    Result getData(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT f.factoryname,cci.fromworkcentername,cci.familylistnames,cci.productname,cci.productdescription,cci.togetherflag,cci.containername,cci.qty,
       ws.workflowstepname outspecname,wfb.workflowname || ':' || wf.workflowrevision workflowinfo,
      (case when csr.recivedate is not null then ws2.workflowstepname when csr.recivedate is null then null end ) inspecname,
      cr.senddate submitdate,
      cr.estimatedcompletedate,
      pdr.createdate denyDate,
      csr.recivedate returndate,
      cr.toacceptdate acceptdate,
      cr.needCompleteDate,
      csr.completeDate,
      e.fullname submitemployee,
      e2.fullname reqemployee,
      e3.fullname returnemployee,
      e4.fullname toacceptor,
      e.employeename sender,mp.liuwei senderliuwei,
      e4.employeename receiver,mp4.liuwei receiverliuwei,
      CASE WHEN pdr.parentid IS NULL AND csr.reciver IS NOT NULL THEN '已转入'
           WHEN pdr.parentid IS NULL AND csr.CompleteDate IS NOT NULL AND csr.recivedate IS NULL THEN '已完成'
           WHEN pdr.parentid IS NULL AND cr.ToAcceptDate IS NOT NULL AND csr.CompleteDate IS NULL AND csr.recivedate IS NULL THEN '已接收'
           WHEN pdr.parentid IS NULL AND cr.ToAcceptDate IS NULL AND cr.senddate IS NOT NULL AND csr.recivedate IS NULL THEN '已转出'
           WHEN pdr.parentid IS NULL AND cr.senddate IS NULL THEN '未转出'
           WHEN pdr.parentid IS NOT NULL THEN '已拒收' END status,
      f2.factoryname tofactoryname,w.workcentername toworkcentername,pdr.reasonnote,e5.fullname denyPerson
FROM ContainerShipRecord cr
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN ContainerShipRecive csr ON csr.containershiprecordid = cr.containershiprecordid
LEFT JOIN containershiprecordstep csdd ON csdd.containershiprecordid = cr.containershiprecordid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csdd.workflowstepid
LEFT JOIN workflow wf ON wf.workflowid = ws.workflowid
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid
LEFT JOIN workflowstep ws2 ON ws2.workflowstepid = cr.toworkflowstepid 
LEFT JOIN employee e ON e.employeeid = cr.sender
LEFT JOIN employee e2 ON e2.employeeid = cr.proposerid
LEFT JOIN employee e3 ON e3.employeeid = csr.reciver
LEFT JOIN employee e4 ON e4.employeeid = cr.toacceptor
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN factory f2 ON f2.factoryid = cr.tofactoryid
LEFT JOIN mes_userimage_mapping mp ON mp.employeename = e.employeename
LEFT JOIN mes_userimage_mapping mp4 ON mp4.employeename = e4.employeename
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
LEFT JOIN employee e5 ON e5.employeeid = pdr.employeeid --拒收人
LEFT JOIN workcenter w ON w.workcenterid = cr.toworkcenterid
WHERE cci.isinstore <> 1 AND cr.isuse = 0 AND cci.containerstatus = 1 ";
        // 分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        // 工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND cci.fromworkcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        // 机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND cci.familylistnames LIKE '%{0}%'", jObject["productfamilyname"].ToString());
        }

        // 转出人
        if (jObject["workerid"] != null && jObject["workerid"].ToString() != "")
        {
            sql += string.Format(" AND cr.sender = '{0}'", jObject["workerid"].ToString());
        }

        // 转工状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "已转出")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND cr.ToAcceptDate IS NULL AND cr.senddate IS NOT NULL AND csr.recivedate IS NULL ");
            }
            if (jObject["status"].ToString() == "已接收")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND cr.ToAcceptDate IS NOT NULL AND csr.CompleteDate IS NULL AND csr.recivedate IS NULL");
            }
            if (jObject["status"].ToString() == "已完成")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND csr.CompleteDate IS NOT NULL AND csr.recivedate IS NULL");
            }
            if (jObject["status"].ToString() == "已转入")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND csr.reciver IS NOT NULL ");
            }
            if (jObject["status"].ToString() == "已拒收")
            {
                sql += string.Format(" AND pdr.parentid IS NOT NULL ");
            }
            if (jObject["status"].ToString() == "未转出")
            {
                sql += string.Format(" AND cr.senddate IS NULL");
            }
        }

        // 零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        // 流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        // 转出时间
        if (jObject["createDate"] != null && jObject["createDate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createDate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND cr.senddate >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND cr.senddate <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        dt.Columns.Add("senderImages", typeof(string));//转出人
        dt.Columns.Add("receiverImages", typeof(string));//接收人

        //查找转出人头像
        dt = u.getUserImage(dt, "senderImages", "sender", "senderliuwei");

        //查找接收人头像
        dt = u.getUserImage(dt, "receiverImages", "receiver", "receiverliuwei");

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = @"
SELECT f.factoryname,cci.fromworkcentername,cci.familylistnames,cci.productname,cci.productdescription,cci.containername,cci.qty,
       f2.factoryname tofactoryname,w.workcentername toworkcentername,ws.workflowstepname outspecname,wfb.workflowname || ':' || wf.workflowrevision workflowinfo,
      (case when csr.recivedate is not null then ws2.workflowstepname when csr.recivedate is null then null end ) inspecname,
      cr.senddate submitdate,
      cr.estimatedcompletedate,
      csr.recivedate returndate,
      cr.toacceptdate acceptdate,
      cr.needCompleteDate,
      csr.completeDate,
      e.fullname submitemployee,
      e2.fullname reqemployee,
      e3.fullname returnemployee,
      e4.fullname toacceptor,
      CASE WHEN pdr.parentid IS NULL AND csr.reciver IS NOT NULL THEN '已转入'
           WHEN pdr.parentid IS NULL AND csr.CompleteDate IS NOT NULL AND csr.recivedate IS NULL THEN '已完成'
           WHEN pdr.parentid IS NULL AND cr.ToAcceptDate IS NOT NULL AND csr.CompleteDate IS NULL AND csr.recivedate IS NULL THEN '已接收'
           WHEN pdr.parentid IS NULL AND cr.ToAcceptDate IS NULL AND cr.senddate IS NOT NULL AND csr.recivedate IS NULL THEN '已转出'
           WHEN pdr.parentid IS NULL AND cr.senddate IS NULL THEN '未转出'
           WHEN pdr.parentid IS NOT NULL THEN '已拒收' END status,
      pdr.createdate denyDate,pdr.reasonnote,e5.fullname denyPerson
FROM ContainerShipRecord cr
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN ContainerShipRecive csr ON csr.containershiprecordid = cr.containershiprecordid
LEFT JOIN containershiprecordstep csdd ON csdd.containershiprecordid = cr.containershiprecordid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csdd.workflowstepid
LEFT JOIN workflow wf ON wf.workflowid = ws.workflowid
LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid
LEFT JOIN workflowstep ws2 ON ws2.workflowstepid = cr.toworkflowstepid 
LEFT JOIN employee e ON e.employeeid = cr.sender
LEFT JOIN employee e2 ON e2.employeeid = cr.proposerid
LEFT JOIN employee e3 ON e3.employeeid = csr.reciver
LEFT JOIN employee e4 ON e4.employeeid = cr.toacceptor
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN factory f2 ON f2.factoryid = cr.tofactoryid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
LEFT JOIN employee e5 ON e5.employeeid = pdr.employeeid --拒收人
LEFT JOIN workcenter w ON w.workcenterid = cr.toworkcenterid
WHERE cci.isinstore <> 1 AND cr.isuse = 0 AND cci.containerstatus = 1 ";
        // 分厂
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", jObject["factoryid"].ToString());
        }

        // 工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            sql += string.Format(" AND cci.fromworkcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        // 机型
        if (jObject["productfamilyname"] != null && jObject["productfamilyname"].ToString() != "")
        {
            sql += string.Format(" AND cci.familylistnames LIKE '%{0}%'", jObject["productfamilyname"].ToString());
        }

        // 转出人
        if (jObject["workerid"] != null && jObject["workerid"].ToString() != "")
        {
            sql += string.Format(" AND cr.sender = '{0}'", jObject["workerid"].ToString());
        }

        // 转工状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "已转出")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND cr.ToAcceptDate IS NULL AND cr.senddate IS NOT NULL AND csr.recivedate IS NULL ");
            }
            if (jObject["status"].ToString() == "已接收")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND cr.ToAcceptDate IS NOT NULL AND csr.CompleteDate IS NULL AND csr.recivedate IS NULL");
            }
            if (jObject["status"].ToString() == "已完成")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND csr.CompleteDate IS NOT NULL AND csr.recivedate IS NULL");
            }
            if (jObject["status"].ToString() == "已转入")
            {
                sql += string.Format(" AND pdr.parentid IS NULL AND csr.reciver IS NOT NULL ");
            }
            if (jObject["status"].ToString() == "已拒收")
            {
                sql += string.Format(" AND pdr.parentid IS NOT NULL ");
            }
            if (jObject["status"].ToString() == "未转出")
            {
                sql += string.Format(" AND cr.senddate IS NULL");
            }
        }

        // 零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        // 流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        // 转出时间
        if (jObject["createDate"] != null && jObject["createDate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createDate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND cr.senddate >= to_date('{0} 00:00:00','yyyy-MM-dd hh24:mi:ss') ", arr[0].ToString());
                sql += string.Format(" AND cr.senddate <= to_date('{0} 23:59:59','yyyy-MM-dd hh24:mi:ss') ", arr[1].ToString());
            }
        }

        DataTable dt = dal.GetDataTable(sql);
        dt.Columns["factoryname"].Caption = "发出分厂";
        dt.Columns["fromworkcentername"].Caption = "工区";
        dt.Columns["familylistnames"].Caption = "机型";
        dt.Columns["productname"].Caption = "零件图号";
        dt.Columns["productdescription"].Caption = "零件名称";
        dt.Columns["containername"].Caption = "流水卡号";
        dt.Columns["qty"].Caption = "在制数";
        dt.Columns["outspecname"].Caption = "转出工序";
        dt.Columns["inspecname"].Caption = "转入工序";
        dt.Columns["submitdate"].Caption = "转出时间";
        dt.Columns["estimatedcompletedate"].Caption = "预计完成时间";
        dt.Columns["denyDate"].Caption = "拒收日期";
        dt.Columns["returndate"].Caption = "转入时间";
        dt.Columns["acceptdate"].Caption = "接收日期";
        dt.Columns["needCompleteDate"].Caption = "要求完成日期";
        dt.Columns["completeDate"].Caption = "完成日期";
        dt.Columns["submitemployee"].Caption = "转出人";
        dt.Columns["reqemployee"].Caption = "申请人";
        dt.Columns["returnemployee"].Caption = "转入人";
        dt.Columns["toacceptor"].Caption = "接收人";
        dt.Columns["status"].Caption = "转工状态";
        dt.Columns["tofactoryname"].Caption = "承制分厂";
        dt.Columns["toworkcentername"].Caption = "承制工区";
        dt.Columns["reasonnote"].Caption = "拒收原因";
        dt.Columns["denyPerson"].Caption = "拒收人";
        dt.Columns["workflowinfo"].Caption = "转出工序工艺";
        ExportHelper.ExportExcel(dt, "转工数据查询报表");
    }
}